<h1 align = 'center'>FACILITY LIST</h1>
<?php
    $this->load->database();
    $roomListQ = 'SELECT DISTINCT RoomAssigned FROM pf_facilities WHERE Status != -1 GROUP BY RoomAssigned';
    $roomListR = $this->db->query($roomListQ);
    if($roomListR->num_rows() > 0)
    {
    
        echo '<table align = "center" border = 5 width = "80%">';
        echo '<tr><th>Room Assigned</th><th>Facility Type</th><th>Facility Name</th><th>Stocks</th><th>Available</th><tr>';
        
        foreach ($roomListR->result() as $roomList)
        {
            $facilityTypeQ = 'SELECT DISTINCT FacilityType FROM pf_facilities WHERE RoomAssigned = \''.$roomList->RoomAssigned.'\' AND Status != -1 GROUP BY FacilityType';
            $facilityTypeR = $this->db->query($facilityTypeQ);
            $totalRow = 0;
            
            foreach($facilityTypeR->result() as $facilityType)
            {
                $facilityNameCountQ = 'SELECT COUNT(DISTINCT FacilityName) as NameCount FROM pf_facilities WHERE RoomAssigned = \''.$roomList->RoomAssigned.'\' AND FacilityType = \''.$facilityType->FacilityType.'\' AND Status != -1';
                $facilityNameCountR = $this->db->query($facilityNameCountQ);
                $facilityNameCount = $facilityNameCountR->result();
                $totalRow = $totalRow + $facilityNameCount[0]->NameCount;
            }
            
            echo '<tr><td rowspan = '.$totalRow.' align = "center">'.$roomList->RoomAssigned.'</td>';
            
            foreach ($facilityTypeR->result() as $facilityType)
            {
                $facilityNameCountQ = 'SELECT COUNT(DISTINCT FacilityName) as NameCount FROM pf_facilities WHERE RoomAssigned = \''.$roomList->RoomAssigned.'\' AND FacilityType = \''.$facilityType->FacilityType.'\' AND Status != -1';
                $facilityNameCountR = $this->db->query($facilityNameCountQ);
                $facilityNameCount = $facilityNameCountR->result();
                
                echo '<td rowspan = '.$facilityNameCount[0]->NameCount.' align = "center">'.$facilityType->FacilityType.'</td>';
                
                $facilityNameQ = 'SELECT DISTINCT FacilityName FROM pf_facilities WHERE RoomAssigned = \''.$roomList->RoomAssigned.'\' AND FacilityType = \''.$facilityType->FacilityType.'\' AND Status != -1';
                $facilityNameR = $this->db->query($facilityNameQ);
                
                foreach ($facilityNameR->result() as $facilityName)
                {
                    echo '<td align = "center">'.$facilityName->FacilityName.'</td>';
                    
                    $stockCountQ = 'SELECT COUNT(FacilityID) as Stock FROM pf_facilities WHERE RoomAssigned = \''.$roomList->RoomAssigned.'\' AND FacilityType = \''.$facilityType->FacilityType.'\' AND FacilityName = \''.$facilityName->FacilityName.'\' AND Status != -1';
                    $stockCountR = $this->db->query($stockCountQ);
                    $stockCount = $stockCountR->result();
                    
                    $stockAvailableQ = 'SELECT COUNT(FacilityID) as Available FROM pf_facilities WHERE RoomAssigned = \''.$roomList->RoomAssigned.'\' AND FacilityType = \''.$facilityType->FacilityType.'\' AND FacilityName = \''.$facilityName->FacilityName.'\' AND Status = 1';
                    $stockAvailableR = $this->db->query($stockAvailableQ);
                    $stockAvailable = $stockAvailableR->result();
                    
                    echo '<td align = "center">'.$stockCount[0]->Stock.'</td>';//output
                    echo '<td align = "center">'.$stockAvailable[0]->Available.'</td></tr>';//output
                }
            }
        }
        echo '</table>';
    }
    else
    {
        echo '<h5 align = "center"><i>no facilities added</i></h5>';
    }
?>